package it.trekker.db;

import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class PoiDAO {

	private static final String TAG = PoiDAO.class.getSimpleName();
	
	private static String[] ALL_FIELDS = new String[] {
		PoiTableMeta.KEY_ROWID,
		PoiTableMeta.KEY_FRIENDLYNAME,
		PoiTableMeta.KEY_LATITUDE, 
		PoiTableMeta.KEY_LONGITUDE, 
		PoiTableMeta.KEY_ALTITUDE,
		PoiTableMeta.KEY_EXCURSIONID };
	
	private static Context context = null;
	private SQLiteDatabase db = null;
	private DatabaseHelper dbHelper = null;

	public PoiDAO(Context context) {
		dbHelper = new DatabaseHelper(context);
	}

	public SQLiteDatabase open() {
		if(db == null || !db.isOpen()) {
			db = dbHelper.getWritableDatabase();
			db.execSQL("PRAGMA foreign_keys=ON;");
		}
		
		return db;
	}

	public void close() {
		if(db != null && db.isOpen())
			db.close();
	}
	
	public boolean isOpen() {
		return db.isOpen();
	}
	
	

	public int insertPoi(Poi poi) {

		ContentValues cv = new ContentValues();

		cv.put(PoiTableMeta.KEY_FRIENDLYNAME, poi.getFriendlyName());
		cv.put(PoiTableMeta.KEY_LATITUDE, poi.getLocation().getLatitude());
		cv.put(PoiTableMeta.KEY_LONGITUDE, poi.getLocation().getLongitude());
		cv.put(PoiTableMeta.KEY_ALTITUDE, poi.getLocation().getAltitude());
		cv.put(PoiTableMeta.KEY_EXCURSIONID, poi.getExcursionId());

		return (int) db.insert(PoiTableMeta.DATABASE_TABLE, null, cv);
	}
	

	public boolean deletePoi(long id) {
		return (db.delete(PoiTableMeta.DATABASE_TABLE, PoiTableMeta.KEY_ROWID + "=" + id, null) > 0);
	}

	
	public List<Poi> getAllPois() {

		List<Poi> result = null;

		Cursor cursor = getAllPoisAsCursor();

		if(cursor != null) {
			result = new ArrayList<Poi>();
			
			while(cursor.moveToNext()) {
				Poi poi = cursorToPoi(cursor);
				result.add(poi);
			}
			cursor.close();
		}

		return result;
	}
	
	
	public Cursor getAllPoisAsCursor() {

		Cursor cursor = db.query(PoiTableMeta.DATABASE_TABLE, 
				ALL_FIELDS,
				null,
				null,
				null,
				null,
				null);

		return cursor;
	}
	
	
	public List<Poi> getAllPoisByExcursion(long excursionId) {

		List<Poi> result = null;

		Cursor cursor = getAllPoisByExcursionAsCursor(excursionId);

		if(cursor != null) {
			result = new ArrayList<Poi>();
			
			while(cursor.moveToNext()) {
				Poi poi = cursorToPoi(cursor);
				result.add(poi);
			}
			cursor.close();
		}

		return result;
	}
	
	
	public Cursor getAllPoisByExcursionAsCursor(long excursionId) {

		String query =  "SELECT * FROM " + 
							PoiTableMeta.DATABASE_TABLE + " poi " +
						" WHERE " +
							PoiTableMeta.KEY_EXCURSIONID + "=?";
		
		Cursor cursor = db.rawQuery(query, new String[] {String.valueOf(excursionId)});

		return cursor;
	}
	

	public Poi getPoi(int id) {

		Poi result = null;

		Cursor cursor = db.query(PoiTableMeta.DATABASE_TABLE,
				ALL_FIELDS,
				null,
				null,
				null,
				null,
				null);

		if (cursor != null) {
			cursor.moveToFirst();

			result = cursorToPoi(cursor);

			cursor.close();
		}

		return result;
	}
	
	
	public boolean updatePoi(Poi poi) {

		ContentValues args = new ContentValues();

		args.put(PoiTableMeta.KEY_FRIENDLYNAME, poi.getFriendlyName());
		args.put(PoiTableMeta.KEY_LATITUDE, poi.getLocation().getLatitude());
		args.put(PoiTableMeta.KEY_LONGITUDE, poi.getLocation().getLongitude());
		args.put(PoiTableMeta.KEY_ALTITUDE, poi.getLocation().getAltitude());

		return (db.update(PoiTableMeta.DATABASE_TABLE, args, PoiTableMeta.KEY_ROWID + "=" + poi.getId(), null) > 0);
	}

	
	private Poi cursorToPoi(Cursor cursor) {

		Poi poi = null;

		if(cursor != null) {
			
			poi = new Poi();
			
			int idCol = cursor.getColumnIndexOrThrow(PoiTableMeta.KEY_ROWID);
			int friendlyNameCol = cursor.getColumnIndexOrThrow(PoiTableMeta.KEY_FRIENDLYNAME);
			int latitudeCol = cursor.getColumnIndexOrThrow(PoiTableMeta.KEY_LATITUDE);
			int longitudeCol = cursor.getColumnIndexOrThrow(PoiTableMeta.KEY_LONGITUDE);
			int altitudeCol = cursor.getColumnIndexOrThrow(PoiTableMeta.KEY_ALTITUDE);

			GPSCoordinate gpsCoord = new GPSCoordinate();
			gpsCoord.setLatitude(cursor.getDouble(latitudeCol));
			gpsCoord.setLongitude(cursor.getDouble(longitudeCol));
			gpsCoord.setAltitude(cursor.getDouble(altitudeCol));
			
			poi.setId(cursor.getInt(idCol));
			poi.setFriendlyName(cursor.getString(friendlyNameCol));
			poi.setLocation(gpsCoord);
			
		}

		return poi;
	}
}
